package com.yonyou.pmclouds.basecom.util;

import com.yonyou.pmclouds.basecom.exception.BusinessException;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.poifs.filesystem.DocumentFactoryHelper;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.IOException;
import java.io.InputStream;
import java.io.PushbackInputStream;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.List;

/**
 * Excel操作工具类
 * @author xiejianyu
 * 2019-09-04
 */
public class ExcelUtil {

    /**
     * 从输入流获取EXCEL内容，目前默认只读取第一个sheet，返回全部内容(包括标题行)
     * 如果单元格存在合并情况，则合并区域只有最左上的单元格有值
     * @param in 输入流
     * @param cols 需要读取的列数，传0默认按照第一行的宽度读取表内容
     * @return 二维string列表
     * @throws Exception 异常
     */
    public static List<List<String>> readExcel(InputStream in, int cols) throws Exception {
        List<List<String>> dataResults = new ArrayList<>();
        Workbook wb;
        try {
            wb = createWorkbook(in);
            Sheet s = wb.getSheetAt(0);
            if (s != null) {
                int size;
                if (cols>0) {
                    size = cols;
                } else {
                    size = s.getRow(0).getLastCellNum();
                }

                for (Row row : s) {
                    List<String> _rowData = new ArrayList<>();
                    for (int i=0; i<size; i++) {
                        Cell cell = row.getCell(i);
                        if (cell==null) {
                            _rowData.add("");
                        } else {
                            String content = getCellContent(cell);
                            if (content==null) {
                                content = "";
                            }
                            _rowData.add(content);
                        }
                    }
                    dataResults.add(_rowData);
                }
            }
        } catch (Exception e) {
            throw e;
        }
        return dataResults;
    }

    /**
     * 从输入流创建EXCEL的workbook
     * @param in 输入流
     * @return workbook
     * @throws IOException IO异常
     * @throws InvalidFormatException 格式异常
     * @throws BusinessException 其他业务异常
     */
    private static Workbook createWorkbook(InputStream in) throws IOException, InvalidFormatException, BusinessException {
        if (!in.markSupported()) {
            in = new PushbackInputStream(in, 8);
        }

        //按照xls解析
        if (POIFSFileSystem.hasPOIFSHeader(in)) {
            return new HSSFWorkbook(in);
        }

        //按照xlsx解析
        if ( DocumentFactoryHelper.hasOOXMLHeader(in)) {
            return new XSSFWorkbook(OPCPackage.open(in));
        }

        throw new BusinessException("不支持的EXCEL文件格式");
    }

    /**
     * 获取单元格的内容
     * @param cell 单元格
     * @return 单元格内容，String形式
     */
    private static String getCellContent(Cell cell) {
        StringBuilder sb = new StringBuilder();
        switch (cell.getCellType()) {
            case Cell.CELL_TYPE_NUMERIC : // 数字，处理excel浮点数字误差、科学计数法等
                cell.setCellType(Cell.CELL_TYPE_STRING);
                String s = cell.getStringCellValue();
                int start = s.indexOf(".");
                if(start>-1){
                    if(s.indexOf("000000", start)>-1||s.indexOf("999999",start)>-1){
                        BigDecimal bd= new BigDecimal(s);
                        double d= bd.setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue();
                        DecimalFormat df = new DecimalFormat("#.00");
                        s =df.format(d);
                    }
                }
                sb.append(s);
                break;
            case Cell.CELL_TYPE_BOOLEAN : // 布尔
                sb.append(cell.getBooleanCellValue());
                break;
            case Cell.CELL_TYPE_FORMULA : // 公式
                sb.append(cell.getCellFormula());
                break;
            case Cell.CELL_TYPE_STRING : // 字符串
                s = cell.getStringCellValue().trim();
                start = s.indexOf(".");
                if(start>-1){
                    if(s.indexOf("000000",start)>-1||s.indexOf("999999",start)>-1){
                        BigDecimal bd= new BigDecimal(s);
                        double d= bd.setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue();
                        DecimalFormat df = new DecimalFormat("#.00");
                        s =df.format(d);
                    }
                }
                sb.append(s);
                break;
            case Cell.CELL_TYPE_BLANK : // 空值
            case Cell.CELL_TYPE_ERROR : // 故障
            default :
                break;
        }
        return sb.toString();
    }

}
